﻿using System;
using System.Collections.Generic;
using System.Text;
using AD.Model;
using System.Data;
using System.Data.SqlClient;
namespace AD.DAL
{
    public class UserDAL : DALBase
    {
        public static IList<UserMod> QueryByPage(string filter, int pageSize, int index, out int maxcount)
        {
            maxcount = 0;
            try
            {
                maxcount = Count(filter);
                //string sql = "select top {0} * from (select top {1} * from tab_users_info order by AutoID asc ) as a order by AutoID desc";
                string sql = "select top {0} * from tab_users_info where (AutoID NOT IN (select top {1} AutoID from tab_users_info order by AutoID)) order by AutoID";
                sql = string.Format(sql, pageSize, index * pageSize);
                if (!string.IsNullOrEmpty(filter)) {
                    sql = "select top {0} * from tab_users_info where {2} and (AutoID NOT IN (select top {1} AutoID from tab_users_info where {2} order by AutoID)) order by AutoID";
                    sql = string.Format(sql,pageSize, index * pageSize, filter);
                }
                DataTable dt = ExecuteDataTable(sql);
                if (dt == null || dt.Rows.Count <= 0) return null;
                IList<UserMod> accountFlows = new List<UserMod>();
                foreach (DataRow row in dt.Rows)
                {
                    UserMod user = SetModel(row);
                    accountFlows.Add(user);
                }
                return accountFlows;
            }
            catch { return null; }
        }

        public static int Sum(string filter) {
            try
            {
                string sql = "Select count(*) from tab_users_info ";
                if (!string.IsNullOrEmpty(filter))
                    sql += " where " + filter;
                SqlDataReader reader = ExecuteReader(sql);
                while (reader.Read())
                {
                    return reader.GetInt32(0);
                }
            }
            catch { }
            return 0;
        }
        public static int Count(string filter)
        {
            return QueryFilterToTable(filter).Rows.Count;
        }

        public static int Insert(UserMod user)
        {
            try
            {
                if (CheckEmail(user) != null) return 0;
                string sql = "insert into tab_users_info(BalancePoint,Recommended,UnderstandChannel,NetShopAddress,NetShopName,Email,Passwd,CompanyName,Url,TelPhone,MobilePhone,QQ,WangWang,Product,IsFrozen,IsValid,RegDate) values(@BalancePoint,@Recommended,@UnderstandChannel,@NetShopAddress,@NetShopName,@Email,@Passwd,@CompanyName,@Url,@TelPhone,@MobilePhone,@QQ,@WangWang,@Product,@IsFrozen,@IsValid,@RegDate)";
                //string sql = "insert into tab_users_info(NetShopAddress,NetShopName,Email,Passwd,CompanyName,Url,TelPhone,MobilePhone,QQ,WangWang,Product,IsFrozen,IsValid,RegDate) values(@NetShopAddress,@NetShopName,@Email,@Passwd,@CompanyName,@Url,@TelPhone,@MobilePhone,@QQ,@WangWang,@Product,@IsFrozen,@IsValid,@RegDate)";
                SqlParameter[] sqlParams = GetSqlParameter(user);
                return ExecuteNonQuery(sql, sqlParams);
            }
            catch
            {
                return 0;
            }
        }

        public static UserMod CheckEmail(UserMod user)
        {
            return CheckEmail(user.Email);
        }

        public static UserMod CheckEmail(string email)
        {
            try
            {
                string sql = string.Format("Select * from tab_users_info where Email='{0}'", email);
                DataTable dt = ExecuteDataTable(sql);
                if (dt == null || dt.Rows.Count <= 0) return null;
                foreach (DataRow row in dt.Rows)
                {
                    return SetModel(row);
                }
            }
            catch
            {
            }
            return null;
        }

        public static UserMod SignUser(UserMod user, out int errorCode)
        {
            errorCode = -10;
            try
            {
                return SignUser(user.Email,out errorCode);
                //string sql = string.Format("Select * from tab_users_info where Email='{0}'", user.Email);
                //DataTable dt = ExecuteDataTable(sql);
                //if (dt == null || dt.Rows.Count <= 0) return null;
                //foreach (DataRow row in dt.Rows)
                //{
                //    return SetModel(row);
                //}
            }
            catch
            {
            }
            return null;
        }

        public static UserMod SignUser(string userName, out int errorCode)
        {
            try
            {
                errorCode = -6;
                string sql = string.Format("Select * from tab_users_info where Email='{0}'", userName);
                DataTable dt = ExecuteDataTable(sql);
                if (dt == null || dt.Rows.Count <= 0) return null;
                foreach (DataRow row in dt.Rows)
                {
                    return SetModel(row);
                }
            }
            catch
            {
                errorCode = -10;
            }
            return null;
        }
        public static UserMod QueryFrist(string filter)
        {
            IList<UserMod> tmp = QueryFilterToList(filter);
            if (tmp != null) return tmp[0];
            return null;
        }

        public static DataTable QueryFilterToTable(string filter)
        {
            try
            {
                string sql = "Select * from tab_users_info ";
                if (!string.IsNullOrEmpty(filter))
                    sql += " where " + filter;
                return ExecuteDataTable(sql);
            }
            catch
            {
            }
            return null;
        }

        public static List<UserMod> QueryFilterToList(string filter)
        {
            try
            {
                DataTable dt = QueryFilterToTable(filter);
                if (dt == null || dt.Rows.Count <= 0) return null;
                List<UserMod> users = new List<UserMod>();
                foreach (DataRow row in dt.Rows)
                {
                    UserMod user = SetModel(row);
                    users.Add(user);
                }
                return users;
            }
            catch
            {
            }
            return null;
        }

        #region 模型

        static UserMod SetModel(DataRow row)
        {
            UserMod user = new UserMod();
            user.AutoID = int.Parse(row["AutoID"].ToString());

            if (row["BalancePoint"] != DBNull.Value)
                user.BalancePoint = int.Parse(row["BalancePoint"].ToString());

            if (row["CompanyName"] != DBNull.Value)
                user.CompanyName = row["CompanyName"].ToString();

            if (row["NetShopAddress"] != DBNull.Value)
                user.NetShopAddress = row["NetShopAddress"].ToString();

            if (row["NetShopName"] != DBNull.Value)
                user.NetShopName = row["NetShopName"].ToString();

            if (row["MobilePhone"] != DBNull.Value)
                user.MobilePhone = row["MobilePhone"].ToString();

            if (row["Email"] != DBNull.Value)
                user.Email = row["Email"].ToString();

            if (row["Passwd"] != DBNull.Value)
                user.Passwd = row["Passwd"].ToString();

            if (row["Product"] != DBNull.Value)
                user.Product = row["Product"].ToString();

            if (row["QQ"] != DBNull.Value)
                user.QQ = row["QQ"].ToString();

            if (row["TelPhone"] != DBNull.Value)
                user.TelPhone = row["TelPhone"].ToString();

            if (row["Recommended"] != DBNull.Value)
                user.Recommended = row["Recommended"].ToString();

            if (row["UnderstandChannel"] != DBNull.Value)
                user.UnderstandChannel = row["UnderstandChannel"].ToString();

            if (row["Url"] != DBNull.Value)
                user.Url = row["Url"].ToString();

            if (row["WangWang"] != DBNull.Value)
                user.WangWang = row["WangWang"].ToString();

            if (row["IsFrozen"] != DBNull.Value)
                user.IsFrozen = bool.Parse(row["IsFrozen"].ToString());

            if (row["IsValid"] != DBNull.Value)
                user.IsValid = bool.Parse(row["IsValid"].ToString());

            if (row["RegDate"] != DBNull.Value)
                user.RegDate = DateTime.Parse(row["RegDate"].ToString());
            return user;
        }

        static SqlParameter[] GetSqlParameter(UserMod user)
        {
            List<SqlParameter> param = new List<SqlParameter>();
            SqlParameter AutoID = new SqlParameter("@AutoID", SqlDbType.Int, 4, "AutoID");
            AutoID.Value = user.AutoID;
            param.Add(AutoID);
            SqlParameter BalancePoint = new SqlParameter("@BalancePoint", SqlDbType.Int, 4, "BalancePoint");
            BalancePoint.Value = user.BalancePoint;
            param.Add(BalancePoint);

            SqlParameter CompanyName = new SqlParameter("@CompanyName", SqlDbType.VarChar, 100, "CompanyName");
            CompanyName.Value = user.CompanyName;
            param.Add(CompanyName);

            SqlParameter Email = new SqlParameter("@Email", SqlDbType.VarChar, 100, "Email");
            Email.Value = user.Email;
            param.Add(Email);

            SqlParameter Passwd = new SqlParameter("@Passwd", SqlDbType.VarChar, 30, "Passwd");
            Passwd.Value = user.Passwd;
            param.Add(Passwd);

            SqlParameter IsFrozen = new SqlParameter("@IsFrozen", SqlDbType.Bit, 1, "IsFrozen");
            IsFrozen.Value = user.IsFrozen;
            param.Add(IsFrozen);

            SqlParameter MobilePhone = new SqlParameter("@MobilePhone", SqlDbType.VarChar, 50, "MobilePhone");
            MobilePhone.Value = user.MobilePhone;
            param.Add(MobilePhone);

            SqlParameter Product = new SqlParameter("@Product", SqlDbType.VarChar, 100, "Product");
            Product.Value = user.Product;
            param.Add(Product);

            SqlParameter QQ = new SqlParameter("@QQ", SqlDbType.VarChar, 30, "QQ");
            QQ.Value = user.QQ;
            param.Add(QQ);

            SqlParameter TelPhone = new SqlParameter("@TelPhone", SqlDbType.VarChar, 30, "TelPhone");
            TelPhone.Value = user.TelPhone;
            param.Add(TelPhone);

            SqlParameter Url = new SqlParameter("@Url", SqlDbType.VarChar, 100, "Url");
            Url.Value = user.Url;
            param.Add(Url);

            SqlParameter WangWang = new SqlParameter("@WangWang", SqlDbType.VarChar, 50, "WangWang");
            WangWang.Value = user.WangWang;
            param.Add(WangWang);

            SqlParameter NetShopAddress = new SqlParameter("@NetShopAddress", SqlDbType.VarChar, 200, "NetShopAddress");
            NetShopAddress.Value = user.NetShopAddress;
            param.Add(NetShopAddress);

            SqlParameter NetShopName = new SqlParameter("@NetShopName", SqlDbType.VarChar, 200, "NetShopName");
            NetShopName.Value = user.NetShopName;
            param.Add(NetShopName);

            SqlParameter IsValid = new SqlParameter("@IsValid", SqlDbType.Bit, 1, "IsValid");
            IsValid.Value = user.IsValid;
            param.Add(IsValid);

            SqlParameter RegDate = new SqlParameter("@RegDate", SqlDbType.DateTime, 8, "RegDate");
            RegDate.Value = user.RegDate;
            param.Add(RegDate);

            SqlParameter Recommended = new SqlParameter("@Recommended", SqlDbType.VarChar, 200, "Recommended");
            Recommended.Value = user.Recommended;
            param.Add(Recommended);

            SqlParameter UnderstandChannel = new SqlParameter("@UnderstandChannel", SqlDbType.VarChar, 200, "UnderstandChannel");
            UnderstandChannel.Value = user.UnderstandChannel;
            param.Add(UnderstandChannel);

            return param.ToArray();
        }

        #endregion

        public static int UpdateOtherInfo(UserMod user)
        {
            try
            {
                string sql = "update tab_users_info set Recommended=@Recommended,UnderstandChannel=@UnderstandChannel,NetShopAddress=@NetShopAddress,NetShopName=@NetShopName,CompanyName=@CompanyName,Url=@Url,TelPhone=@TelPhone,MobilePhone=@MobilePhone,QQ=@QQ,WangWang=@WangWang,Product=@Product where AutoID=@AutoID";
                SqlParameter[] sqlParams = GetSqlParameter(user);
                return ExecuteNonQuery(sql, sqlParams);
            }
            catch { }
            return 0;
        }

        public static int UpdateValid(int userId)
        {
            try
            {
                string sql = "update tab_users_info set IsValid=1 where AutoID=" + userId;
                return ExecuteNonQuery(sql, null);
            }
            catch { }
            return 0;
        }

        public static int UpdatePasswd(UserMod user)
        {
            try
            {
                string sql = "update tab_users_info set Passwd=@Passwd where AutoID=@AutoID";
                SqlParameter[] sqlParams = GetSqlParameter(user);
                return ExecuteNonQuery(sql, sqlParams);
            }
            catch { }
            return 0;
        }

        internal static int UpdateBalancePoint(SqlCommand sqlCommand, SqlTransaction sqlTransaction, UserMod user, int balancePoint)
        {
            return UpdateBalancePoint(sqlCommand, sqlTransaction, user.AutoID, balancePoint);
        }

        internal static int UpdateBalancePoint(SqlCommand sqlCommand, SqlTransaction sqlTransaction, int autoId, int balancePoint)
        {
            string sql = "update tab_users_info set BalancePoint=" + balancePoint + "+BalancePoint where AutoID=" + autoId;
            PrepareCommand(sqlCommand, sqlTransaction, sql);
            return sqlCommand.ExecuteNonQuery();
        }

        public static int UpdateBalancePoint(int autoId, int balancePoint)
        {
            string sql = "update tab_users_info set BalancePoint=" + balancePoint + "+BalancePoint where AutoID=" + autoId;
            return ExecuteNonQuery(sql);
        }
        public static int SetBalancePoint(int autoId, int balancePoint)
        {
            string sql = "update tab_users_info set BalancePoint=" + balancePoint + " where AutoID=" + autoId;
            return ExecuteNonQuery(sql);
        }
        public static int Delete(string filter)
        {
            try
            {
                string sql = "delete from tab_users_info ";
                if (!string.IsNullOrEmpty(filter))
                    sql += " where " + filter;
                return ExecuteNonQuery(sql);
            }
            catch { }
            return 0;
        }

        public static int Delete(UserMod user)
        {
            try
            {
                string sql = string.Format(" AutoID={0}", user.AutoID);
                return Delete(sql);
            }
            catch { }
            return 0;
        }
    }
}